﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Data;

namespace NutriFast
{
    class dbPeri
    {
        //Conection String
        public static string connString = connString = ConfigurationManager.ConnectionStrings["conn"].ConnectionString;



        public static bool AddReceta(Receta r)
        {
            //Variables locales 
            bool insertado = false;
            SqlConnection conn = new SqlConnection(connString);
            conn.Open();
            SqlCommand sqlCommand = new SqlCommand("spPeriquinAddReceta", conn);
            sqlCommand.CommandType = CommandType.StoredProcedure;
            sqlCommand.Parameters.AddWithValue("@rName", r.Nreceta);


            try
            {
                sqlCommand.ExecuteNonQuery();
                insertado = true;
            }
            catch (Exception e)
            {
                System.Console.WriteLine(e.StackTrace);
            }
            conn.Close();
            return insertado;

        }//fin de addreceta

        
        public static bool AddRecetaMeal(int idR,int  idM,int idD)
        {
            //Variables locales
            bool insertado = false;
            SqlConnection conn = new SqlConnection(connString);
            conn.Open();
            SqlCommand sqlCommand = new SqlCommand("spPeriquinAddRecetaMeal", conn);
            sqlCommand.CommandType = CommandType.StoredProcedure;
            sqlCommand.Parameters.AddWithValue("@idReceta", idR);
            sqlCommand.Parameters.AddWithValue("@idMeal", idM);
            sqlCommand.Parameters.AddWithValue("@idDiet", idD);

            try
            {
                sqlCommand.ExecuteNonQuery();
                insertado = true;
            }
            catch (Exception e)
            {
                System.Console.WriteLine(e.StackTrace);
            }
            conn.Close();
            return insertado;

        }//fin de addreceta

        public static bool AddMealFood(int idF, int idM, int idD)
        {
            //Variables locales
            bool insertado = false;
            SqlConnection conn = new SqlConnection(connString);
            conn.Open();
            SqlCommand sqlCommand = new SqlCommand("spPeriquinAddMealFood", conn);
            sqlCommand.CommandType = CommandType.StoredProcedure;
            sqlCommand.Parameters.AddWithValue("@idFood", idF);
            sqlCommand.Parameters.AddWithValue("@idMeal", idM);
            sqlCommand.Parameters.AddWithValue("@idDiet", idD);

            try
            {
                sqlCommand.ExecuteNonQuery();
                insertado = true;
            }
            catch (Exception e)
            {
                System.Console.WriteLine(e.StackTrace);
            }
            conn.Close();
            return insertado;

        }//fin de addreceta

        public static bool AddRecetaFood(Receta_Food rf)
        {
            //Variables locales
            bool insertado = false;
            SqlConnection conn = new SqlConnection(connString);
            conn.Open();
            SqlCommand sqlCommand = new SqlCommand("spPeriquinAddRecetaFood", conn);
            sqlCommand.CommandType = CommandType.StoredProcedure;
            sqlCommand.Parameters.AddWithValue("@idReceta", rf.idReceta);
            sqlCommand.Parameters.AddWithValue("@idFood", rf.idFood);

            try
            {
                sqlCommand.ExecuteNonQuery();
                insertado = true;
            }
            catch (Exception e)
            {
                System.Console.WriteLine(e.StackTrace);
            }
            conn.Close();
            return insertado;

        }//fin de addreceta

        public static int GetIdReceta(string Nreceta)
        {

            SqlConnection conn = new SqlConnection(connString);
            conn.Open();
            SqlCommand sqlCommand = new SqlCommand("spPeriquinGetIdReceta", conn);
            sqlCommand.CommandType = CommandType.StoredProcedure;
            sqlCommand.Parameters.AddWithValue("@nReceta", Nreceta);

            DataSet dsPeriquinGetIdReceta = new DataSet();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dsPeriquinGetIdReceta);


            int idReceta = (int)dsPeriquinGetIdReceta.Tables[0].Rows[0]["idReceta"];


            conn.Close();
            return idReceta;
        }

        public static float Kcal(int idD)
        {

            SqlConnection conn = new SqlConnection(connString);
            conn.Open();
            SqlCommand sqlCommand = new SqlCommand("spPeriquinKcal", conn);
            sqlCommand.CommandType = CommandType.StoredProcedure;
            sqlCommand.Parameters.AddWithValue("@idDiet", idD);

            DataSet dsPeriquinGetIdReceta = new DataSet();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dsPeriquinGetIdReceta);


            float kcal = (float)((double)dsPeriquinGetIdReceta.Tables[0].Rows[0]["KcalT"]);


            conn.Close();
            return kcal;
        }


        public static float porcentage(int idM, int idD)
        {

            SqlConnection conn = new SqlConnection(connString);
            conn.Open();
            SqlCommand sqlCommand = new SqlCommand("spPeriquinPorcentaje", conn);
            sqlCommand.CommandType = CommandType.StoredProcedure;
            sqlCommand.Parameters.AddWithValue("@idMeal", idM);
            sqlCommand.Parameters.AddWithValue("@idDiet", idD);

            DataSet dsPeriquinGetIdReceta = new DataSet();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dsPeriquinGetIdReceta);


            float perc = (float)((double)dsPeriquinGetIdReceta.Tables[0].Rows[0]["percentage"]);


            conn.Close();
            return perc;
        }

        public static bool DelReceta(int idR)
        {
            bool insertado = false;
            SqlConnection conn = new SqlConnection(connString);
            conn.Open();
            SqlCommand sqlCommand = new SqlCommand("spPeriquinDelReceta", conn);
            sqlCommand.CommandType = CommandType.StoredProcedure;
            sqlCommand.Parameters.AddWithValue("@idReceta", idR);

            DataSet dsPeriquinDelReceta = new DataSet();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dsPeriquinDelReceta);

            try
            {
                sqlCommand.ExecuteNonQuery();
                insertado = true;
            }
            catch (Exception e)
            {
               System.Console.WriteLine(e.StackTrace);
            }

            conn.Close();
            return insertado;
        }

        public static bool DelRecetaFood(int idR)
        {
            bool insertado = false;
            SqlConnection conn = new SqlConnection(connString);
            conn.Open();
            SqlCommand sqlCommand = new SqlCommand("spPeriquinDelRecetaFood", conn);
            sqlCommand.CommandType = CommandType.StoredProcedure;
            sqlCommand.Parameters.AddWithValue("@idReceta", idR);

            DataSet dsPeriquinDelReceta = new DataSet();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dsPeriquinDelReceta);

            try
            {
                sqlCommand.ExecuteNonQuery();
                insertado = true;
            }
            catch (Exception e)
            {
                   System.Console.WriteLine(e.StackTrace);
            }

            conn.Close();
            return insertado;
        }
        ///////////////////Vista comidas////////////////////////////////////
        public static int GetEnergia(int idGroup)
        {

            SqlConnection conn = new SqlConnection(connString);
            conn.Open();
            SqlCommand sqlCommand = new SqlCommand("spPeriquinGetGroupEnergia", conn);
            sqlCommand.CommandType = CommandType.StoredProcedure;
            sqlCommand.Parameters.AddWithValue("@idGroup", idGroup);

            DataSet dsPeriquinGetGroupEnergia = new DataSet();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dsPeriquinGetGroupEnergia);


            int Energia = (int)dsPeriquinGetGroupEnergia.Tables[0].Rows[0]["Energia"];


            conn.Close();
            return Energia;
        }

        public static int GetProteina(int idGroup)
        {

            SqlConnection conn = new SqlConnection(connString);
            conn.Open();
            SqlCommand sqlCommand = new SqlCommand("spPeriquinGetGroupProteina", conn);
            sqlCommand.CommandType = CommandType.StoredProcedure;
            sqlCommand.Parameters.AddWithValue("@idGroup", idGroup);

            DataSet dsPeriquinGetGroupProteina = new DataSet();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dsPeriquinGetGroupProteina);


            int Proteina = (int)dsPeriquinGetGroupProteina.Tables[0].Rows[0]["Proteina"];


            conn.Close();
            return Proteina;
        }

        public static int GetLipidos(int idGroup)
        {

            SqlConnection conn = new SqlConnection(connString);
            conn.Open();
            SqlCommand sqlCommand = new SqlCommand("spPeriquinGetGroupLipidos", conn);
            sqlCommand.CommandType = CommandType.StoredProcedure;
            sqlCommand.Parameters.AddWithValue("@idGroup", idGroup);

            DataSet dsPeriquinGetGroupLipidos = new DataSet();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dsPeriquinGetGroupLipidos);


            int Lipidos = (int)dsPeriquinGetGroupLipidos.Tables[0].Rows[0]["Lipidos"];


            conn.Close();
            return Lipidos;
        }

        public static int GetCHO(int idGroup)
        {

            SqlConnection conn = new SqlConnection(connString);
            conn.Open();
            SqlCommand sqlCommand = new SqlCommand("spPeriquinGetGroupHCO", conn);
            sqlCommand.CommandType = CommandType.StoredProcedure;
            sqlCommand.Parameters.AddWithValue("@idGroup", idGroup);

            DataSet dsPeriquinGetGroupHCO = new DataSet();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dsPeriquinGetGroupHCO);


            int CHO = (int)dsPeriquinGetGroupHCO.Tables[0].Rows[0]["hidratos de carbono"];


            conn.Close();
            return CHO;
        }
        ///////////////////////meal//////////////////

        public static bool UpdateMeal(int idM, int idD, float Verduras, float Frutas, float Csg, float Ccg, float Leg, 
                                      float Caa, float Cab, float Cac, float Cad, float Lea, float Leb, float Lec,
                                      float Leca, float Grsp, float Grcp, float Azsgr, float Azcgr, float Albde,
                                      float Bebalc)
        {
            bool Updated = false;
            SqlConnection conn = new SqlConnection(connString);
            conn.Open();
            SqlCommand sqlCommand = new SqlCommand("spPeriquinAddMeal", conn);
            sqlCommand.CommandType = CommandType.StoredProcedure;
            sqlCommand.Parameters.AddWithValue("@idMeal", idM);
            sqlCommand.Parameters.AddWithValue("@idDiet", idD);
            sqlCommand.Parameters.AddWithValue("@Verduras", Verduras);
            sqlCommand.Parameters.AddWithValue("@Frutas", Frutas);
            sqlCommand.Parameters.AddWithValue("@Csg", Csg);
            sqlCommand.Parameters.AddWithValue("@Ccg", Ccg);
            sqlCommand.Parameters.AddWithValue("@Leg", Leg);
            sqlCommand.Parameters.AddWithValue("@Caa", Caa);
            sqlCommand.Parameters.AddWithValue("@Cab", Cab);
            sqlCommand.Parameters.AddWithValue("@Cac", Cac);
            sqlCommand.Parameters.AddWithValue("@Cad", Cad);
            sqlCommand.Parameters.AddWithValue("@Lea", Lea);
            sqlCommand.Parameters.AddWithValue("@Leb", Leb);
            sqlCommand.Parameters.AddWithValue("@Lec", Lec);
            sqlCommand.Parameters.AddWithValue("@Leca", Leca);
            sqlCommand.Parameters.AddWithValue("@Grsp", Grsp);
            sqlCommand.Parameters.AddWithValue("@Grcp", Grcp);
            sqlCommand.Parameters.AddWithValue("@Azsgr", Azsgr);
            sqlCommand.Parameters.AddWithValue("@Azcgr", Azcgr);
            sqlCommand.Parameters.AddWithValue("@Albde", Albde);
            sqlCommand.Parameters.AddWithValue("@Bebalc", Bebalc);

            DataSet dsPeriquinAddMeal = new DataSet();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dsPeriquinAddMeal);

            try
            {
                sqlCommand.ExecuteNonQuery();
                Updated = true;
            }
            catch (Exception e)
            {
                System.Console.WriteLine(e.StackTrace);
            }

            conn.Close();
            return Updated;
        }

        public static bool GetUnitsMeal(Meal m)
        {
            bool Updated = false;
            SqlConnection conn = new SqlConnection(connString);
            conn.Open();
            SqlCommand sqlCommand = new SqlCommand("spPeriquinGetUnitsMeal", conn);
            sqlCommand.CommandType = CommandType.StoredProcedure;
            sqlCommand.Parameters.AddWithValue("@idMeal", m.idM);
            sqlCommand.Parameters.AddWithValue("@idDiet", m.idD);
            

            DataSet dsPeriquinAddMeal = new DataSet();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dsPeriquinAddMeal);
            m.Verduras = float.Parse(dsPeriquinAddMeal.Tables[0].Rows[0]["pVerdura"].ToString());
            m.Frutas =float.Parse(dsPeriquinAddMeal.Tables[0].Rows[0]["pFruta"].ToString());
            m.Csg = float.Parse(dsPeriquinAddMeal.Tables[0].Rows[0]["pCsg"].ToString());
            m.Ccg = float.Parse(dsPeriquinAddMeal.Tables[0].Rows[0]["pCcg"].ToString());
            m.Leg = float.Parse(dsPeriquinAddMeal.Tables[0].Rows[0]["pLeg"].ToString());
            m.Caa = float.Parse(dsPeriquinAddMeal.Tables[0].Rows[0]["pCaa"].ToString());
            m.Cab = float.Parse(dsPeriquinAddMeal.Tables[0].Rows[0]["pCab"].ToString());
            m.Cac = float.Parse(dsPeriquinAddMeal.Tables[0].Rows[0]["pCac"].ToString());
            m.Cac = float.Parse(dsPeriquinAddMeal.Tables[0].Rows[0]["pCad"].ToString());
            m.Lea = float.Parse(dsPeriquinAddMeal.Tables[0].Rows[0]["pLea"].ToString());
            m.Leb = float.Parse(dsPeriquinAddMeal.Tables[0].Rows[0]["pLeb"].ToString());
            m.Lec = float.Parse(dsPeriquinAddMeal.Tables[0].Rows[0]["pLec"].ToString());
            m.Leca = float.Parse(dsPeriquinAddMeal.Tables[0].Rows[0]["pLeca"].ToString());
            m.Grsp = float.Parse(dsPeriquinAddMeal.Tables[0].Rows[0]["pGrsp"].ToString());
            m.Grcp = float.Parse(dsPeriquinAddMeal.Tables[0].Rows[0]["pGrcp"].ToString());
            m.Azsgr = float.Parse(dsPeriquinAddMeal.Tables[0].Rows[0]["pAzsgr"].ToString());
            m.Azcgr = float.Parse(dsPeriquinAddMeal.Tables[0].Rows[0]["pAzcgr"].ToString());
            m.Albde = float.Parse(dsPeriquinAddMeal.Tables[0].Rows[0]["pAlLdE"].ToString());
            m.Bebalc = float.Parse(dsPeriquinAddMeal.Tables[0].Rows[0]["pBebAlc"].ToString());



            try
            {
                sqlCommand.ExecuteNonQuery();
                Updated = true;
            }
            catch (Exception e)
            {
                System.Console.WriteLine(e.StackTrace);
            }

            conn.Close();
            return Updated;
        }

    }
}
